package com.b2c.repository.oms;

import com.b2c.entity.result.PagingResponse;
import com.b2c.common.utils.DateUtil;
import com.b2c.entity.apitao.DcTmallOrderCostEntity;
import com.b2c.entity.apitao.TaoOrderSalesDataEntity;
import com.b2c.entity.apitao.UpdOrderTagReq;
import com.b2c.entity.datacenter.DcTmallOrderEntity;
import com.b2c.entity.datacenter.DcTmallOrderItemEntity;
import com.b2c.entity.erp.ErpSalesOrderItemEntity;
import com.b2c.entity.query.TmallOrderCostQuery;
import com.b2c.entity.erp.ErpGoodsSpecEntity;
import com.b2c.entity.ErpGoodsStockInfoEntity;
import com.b2c.entity.erp.enums.ErpOrderSourceEnum;
import com.b2c.entity.mall.OrderCancelItemEntity;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.tao.TmallOrderRefundVo;
import com.b2c.entity.vo.OrderImportPiPiEntity;
import com.b2c.entity.vo.RefundOrderListVo;
import com.b2c.entity.enums.EnumErpOrderSendStatus;
import com.b2c.entity.enums.erp.EnumOrderReturnStatus;
import com.b2c.entity.enums.tao.EnumDcTmallOrderReturnStatus;
import com.b2c.entity.enums.third.EnumTmallOrderStatus;
import com.b2c.entity.query.OrderItemSwapReq;
import com.b2c.entity.query.TaoOrderQuery;
import com.b2c.repository.Tables;
import com.b2c.entity.vo.erp.ErpSalesOrderDetailVo;
import com.b2c.entity.vo.finance.FinanceOrderItemListVo;
import com.b2c.entity.vo.finance.FinanceOrderListVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
/**
 * 描述：
 * 天猫订单Repository
 *
 * @author qlp
 * @date 2019-10-23 15:23
 */
@Repository
public class DcTmallOrderRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    //查询分页总页数
    protected int getTotalSize() { return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class); }
    /**
     * 批量excel导入天猫订单
     *
     * @param orderList
     * @return
     */
//    @Transactional
//    public ResultVo<Integer> importExcelOrder(EnumTmallOrderSource orderSource, Integer shopId, Integer orderType, Integer developerId, Integer clientId, List<DcTmallOrderEntity> orderList) {
//        if (orderList == null || orderList.size() == 0) return new ResultVo<>(EnumResultVo.DataError, "没有数据");
//
//        /*******插入数据********/
//        int totalSuccess = 0;
//        for (var order : orderList) {
//            try {
//                //查询订单是否存在
//                var oList = jdbcTemplate.query("SELECT * FROM " + Tables.DcTmallOrder + " WHERE id=? "
//                        , new BeanPropertyRowMapper<>(DcTmallOrderEntity.class), Long.parseLong(order.getId()));
//                if (oList == null || oList.size() == 0) {
//
//                    /**************1、新增tmall_order数据**********************/
//                    //不存在，新增订单
//                    String insertSQL = "INSERT INTO " + Tables.DcTmallOrder + " (id,buyerName,totalAmount,shippingFee,discountAmount,payAmount,discountRemark" +
//                            ",createTime,modifyTime,payTime,deliveredTime,completeTime" +
//                            ",sellerMemo,buyerFeedback,statusStr,status" +
//                            ",logisticsCompany,logisticsCode,auditStatus,orderSource,createOn,shopId) " +
//                            " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//
//                    jdbcTemplate.update(insertSQL, order.getId(), order.getBuyerName(), order.getTotalAmount(), order.getShippingFee(), order.getDiscountAmount(), order.getPayAmount(), order.getDiscountRemark(),
//                            order.getCreateTime(), new Date(), order.getPayTime(), order.getDeliveryTime(), order.getCompleteTime(),
//                            order.getSellerMemo(), order.getBuyerFeedback(), order.getStatusStr(), order.getStatus(),
//                            order.getLogisticsCompany(), order.getLogisticsCode(), 0, orderSource.getIndex(), System.currentTimeMillis() / 1000, shopId);
//
//                    //添加订单收货地址
//                    String addressSQL = "INSERT INTO " + Tables.DcTmallOrderAddress + " (orderId,contactPerson,mobile,province,city,area,address) VALUE (?,?,?,?,?,?,?)";
//                    jdbcTemplate.update(addressSQL, order.getId(), order.getContactPerson(), order.getMobile(), order.getProvince(), order.getCity(), order.getArea(), order.getAddress());
//
//                    /**********2、添加dc_order***********/
//                    String dcOrderSQL = "INSERT INTO dc_order (orderNum,orderId,orderType,shopId,developerId,clientId,createOn) VALUE (?,?,?,?,?,?,?)";
//                    KeyHolder keyHolder = new GeneratedKeyHolder();
//                    jdbcTemplate.update(new PreparedStatementCreator() {
//                        @Override
//                        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
//                            PreparedStatement ps = connection.prepareStatement(dcOrderSQL.toString(), Statement.RETURN_GENERATED_KEYS);
//                            ps.setString(1, order.getId());
//                            ps.setLong(2, Long.parseLong(order.getId()));
//                            ps.setInt(3, orderType);
//                            ps.setInt(4, shopId);
//                            ps.setInt(5, developerId);
//                            ps.setInt(6, clientId);
//                            ps.setLong(7, System.currentTimeMillis() / 1000);
//                            return ps;
//                        }
//                    }, keyHolder);
//
//                    Long dcOrderId = keyHolder.getKey().longValue();
//
//                    /*******************5、添加dc_order_item**************************/
//                    String dcOrderItemSQL = "INSERT INTO dc_order_item (orderId,title,img,specNumber,quantity,skuInfo) VALUE (?,?,?,?,?,?)";
//
//                    /*******************3、添加tmall_order_item**************************/
//                    //添加订单明细
//                    String itemSQL = "INSERT INTO " + Tables.DcTmallOrderItem + " (orderId,subItemId,itemAmount" +
//                            ",goodsTitle,goodsNumber,productImgUrl,productUrl,productId,skuId,specNumber,skuInfo,price,quantity," +
//                            "status,statusStr,refundStatus,refundStatusStr) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//                    for (var item : order.getItems()) {
//                        /*******************3、添加tmall_order_item**************************/
//                        double itemAmount = item.getPrice().doubleValue() * item.getQuantity();
//                        jdbcTemplate.update(itemSQL, order.getId(), item.getSubItemId(), itemAmount,
//                                item.getGoodsTitle(), item.getGoodsNumber(), item.getProductImgUrl(), item.getProductUrl(), item.getProductId(), item.getSkuId(), item.getSpecNumber(), item.getSkuInfo(),
//                                item.getPrice(), item.getQuantity(), item.getStatus(), item.getStatusStr(), item.getRefundStatus(), item.getRefundStatusStr());
//
//                        /*******************3、添加dc_order_item**************************/
//                        jdbcTemplate.update(dcOrderItemSQL, dcOrderId, item.getGoodsTitle(), item.getProductImgUrl(), item.getSpecNumber(), item.getQuantity(), item.getSkuInfo());
//                    }
//                    totalSuccess++;
//                }
//
//            } catch (Exception e) {
//                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//                return new ResultVo<>(EnumResultVo.SystemException, "系统异常：" + e.getMessage());
//            }
//        }
//
////        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS", totalSuccess);
//    }

    /**
     * 插入天猫订单
     *
     * @param shopId
     * @param order
     * @return
     */
    @Transactional
    public ResultVo<Integer> updateOrder(Integer shopId, DcTmallOrderEntity order) {
        //查询订单是否存在
        var oList = jdbcTemplate.query("SELECT * FROM " + Tables.DcTmallOrder + " WHERE id=? ", new BeanPropertyRowMapper<>(DcTmallOrderEntity.class), Long.parseLong(order.getId()));
        if (oList != null && oList.size() > 0) {
            //存在，更新
            /**********1、更新订单状态**********/
            String updSQL = "UPDATE " + Tables.DcTmallOrder + " SET totalAmount=?,shippingFee=?,payAmount=?" +
                    ",createTime=?,modifyTime=?,payTime=?,sellerMemo=?,buyerFeedback=?,statusStr=?,status=?,is_comment=? WHERE id=?";

            jdbcTemplate.update(updSQL, order.getTotalAmount(), order.getShippingFee(), order.getPayAmount(),
                    order.getCreateTime(), order.getModifyTime(), order.getPayTime(),
                    order.getSellerMemo(), order.getBuyerFeedback(), order.getStatusStr(), order.getStatus(),order.getIsComment(),order.getId());

            /**********2、更新订单items**********/


            //重新添加dc_tmall_order_item
            String itemSQL1 = "UPDATE " + Tables.DcTmallOrderItem + " SET itemAmount=?" +
                    ",goodsTitle=?,goodsNumber=?,productImgUrl=?,productUrl=?,specNumber=?,skuInfo=?," +
                    "price=?,quantity=?,status=?,statusStr=?,refundStatus=?,refundStatusStr=?," +
                    "discount_fee=?,adjust_fee=?,productId=? WHERE subItemId=? AND orderId=? ";

//            Integer totalQuantity=0;//商品总数
            for (var item : order.getItems()) {
                /*******************2.2、添加tmall_order_item**************************/
//                double itemAmount = item.getPrice().doubleValue() * item.getQuantity();
                jdbcTemplate.update(itemSQL1, item.getItemAmount(),
                        item.getGoodsTitle(), item.getGoodsNumber(), item.getProductImgUrl(), item.getProductUrl(), item.getSpecNumber(), item.getSkuInfo(),
                        item.getPrice(), item.getQuantity(), item.getStatus(), item.getStatusStr(), item.getRefundStatus(), item.getRefundStatusStr(),
                        item.getDiscountFee(), item.getAdjustFee(),item.getProductId(),
                        item.getSubItemId(), order.getId());
                //totalQuantity += item.getQuantity().intValue();
            }

            /**********3、更新订单收货地址（暂时不做更新）**********/

            return new ResultVo<>(EnumResultVo.DataExist, "订单已经存在，并且更新成功");
        } else {
            try {
                /**************1、新增tmall_order数据**********************/
                //不存在，新增订单
                String insertSQL = "INSERT INTO " + Tables.DcTmallOrder + " (id,buyerName,totalAmount,shippingFee,discountAmount,payAmount,discountRemark" +
                        ",createTime,modifyTime,payTime" +
                        ",sellerMemo,buyerFeedback,statusStr,status" +
                        ",auditStatus,orderSource,createOn,shopId,is_comment) " +
                        " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

                jdbcTemplate.update(insertSQL, order.getId(), order.getBuyerName(), order.getTotalAmount(), order.getShippingFee(), 0.00, order.getPayAmount(), "",
                        order.getCreateTime(), order.getModifyTime(), order.getPayTime(),
                        order.getSellerMemo(), order.getBuyerFeedback(), order.getStatusStr(), order.getStatus(),
                        0, 0, System.currentTimeMillis() / 1000, shopId,order.getIsComment());

                //添加订单收货地址ngc
                String addressSQL = "INSERT INTO " + Tables.DcTmallOrderAddress + " (orderId,contactPerson,mobile,province,city,area,address) VALUE (?,?,?,?,?,?,?)";
                jdbcTemplate.update(addressSQL, order.getId(),order.getContactPerson() ,order.getMobile(), order.getProvince(), order.getCity(), order.getArea(),order.getAddress());

//                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                /*******************3、添加tmall_order_item**************************/
                //添加订单明细
                String itemSQL = "INSERT INTO " + Tables.DcTmallOrderItem + " (orderId,subItemId,itemAmount" +
                        ",goodsTitle,goodsNumber,productImgUrl,productUrl,productId,skuId,specNumber,skuInfo,price,quantity," +
                        "status,statusStr,refundStatus,refundStatusStr,discount_fee,adjust_fee,erpGoodsId,erpGoodsSpecId) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

                Integer totalQuantity = 0;//商品总数
                double goodsTotalAmount = 0l;//商品总价
                for (var item : order.getItems()) {

                    /*******************3、添加tmall_order_item**************************/

                    /****查询sku*****/
                    Integer goodsId = 0;
                    Integer goodsSpecId = 0;
                    //查询erp商品规格信息
                    if(StringUtils.isEmpty(item.getSpecNumber())==false) {
                        try {
                            ErpGoodsSpecEntity erpGoodsSpec = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getSpecNumber());
                            goodsId = erpGoodsSpec.getGoodsId();
                            goodsSpecId = erpGoodsSpec.getId();
                        } catch (Exception E) {
                        }
                    }
                    if(item.getProductId()==null ) item.setProductId("0");
                    jdbcTemplate.update(itemSQL,
                     order.getId(),
                      item.getSubItemId(), 
                      item.getItemAmount(),
                            item.getGoodsTitle(), 
                            item.getGoodsNumber(), 
                            item.getProductImgUrl(), 
                            item.getProductUrl(), 
                            Long.valueOf(item.getProductId()), 
                            0, item.getSpecNumber(), 
                            item.getSkuInfo(),
                            item.getPrice(), 
                            item.getQuantity(), 
                            item.getStatus(), 
                            item.getStatusStr(), 
                            item.getRefundStatus(), 
                            item.getRefundStatusStr(), 
                            item.getDiscountFee(), 
                            item.getAdjustFee()
                    ,goodsId,
                    goodsSpecId);

                    totalQuantity += item.getQuantity().intValue();
                    goodsTotalAmount += item.getPrice().doubleValue() * item.getQuantity();
                }


            } catch (Exception e) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return new ResultVo<>(EnumResultVo.SystemException, "系统异常：" + e.getMessage());
            }
        }
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }


    /**
     * 获取淘系订单 根据店铺id
     *
     * @param pageIndex
     * @param pageSize
     * @param orderId
     * @param status
     * @param shopId
     * @param receiverMobile
     * @param startTime
     * @param endTime
     * @return
     */
    @Transactional
    public PagingResponse<DcTmallOrderEntity> getList(Integer pageIndex, Integer pageSize, String orderId, Integer status, Integer shopId, String receiverMobile, Integer developerId, String startTime, String endTime) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS o.*,a.contactPerson,a.mobile,a.province,a.city,a.area,a.address  ");
        sb.append(" FROM ").append(Tables.DcTmallOrder).append(" o ");
        sb.append(" LEFT JOIN ").append(Tables.DcTmallOrderAddress).append(" a on a.orderId=o.id ");
  

        sb.append(" WHERE o.shopId=? ");
        params.add(shopId);

        if (StringUtils.isEmpty(orderId) == false) {
            sb.append(" AND o.id=? ");
            params.add(orderId);
        }

        if (status != null) {
            sb.append(" AND o.status = ? ");
            params.add(status);
        }

        if (StringUtils.isEmpty(receiverMobile) == false) {
            sb.append(" AND a.mobile = ?");
            params.add(receiverMobile);
        }
        if (StringUtils.isEmpty(startTime) == false) {
            sb.append(" AND o.createTime > ?");
            params.add(startTime);
        }
        if (StringUtils.isEmpty(endTime) == false) {
            sb.append(" AND o.createTime <= ?");
            params.add(endTime);
        }
        if (developerId != null && developerId > 0) {
            sb.append(" AND o.developerId = ? ");
            params.add(developerId);
        }

        sb.append(" ORDER BY o.createTime desc LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(DcTmallOrderEntity.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();

        if (list != null && list.size() > 0) {
            //查询item
            for (var order : list) {
                order.setItems(jdbcTemplate.query("SELECT * FROM " + Tables.DcTmallOrderItem + " WHERE orderId=?", new BeanPropertyRowMapper<>(DcTmallOrderItemEntity.class), order.getId()));
            }
        }
        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    public PagingResponse<DcTmallOrderEntity> getNewList(TaoOrderQuery reqData) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS o.*  ");
        sb.append(",a.contactPerson,a.mobile,a.province,a.city,a.area,a.address ");
        sb.append(",IFNULL(developer.user_name,developer.nick_name) as developerName ");//业务员信息
        sb.append(",(select IFNULL(sum(amount),0.00) FROM dc_tmall_order_cost where dc_tmall_order_id=o.id) as salesAmount ");
        sb.append(" FROM ").append(Tables.DcTmallOrder).append(" o ");
        sb.append(" LEFT JOIN ").append(Tables.DcTmallOrderAddress).append(" a on a.orderId=o.id ");
        sb.append(" LEFT JOIN ").append(Tables.User).append(" as developer on developer.id = o.developerId");

        sb.append(" WHERE o.shopId=? ");
        params.add(reqData.getShopId());

        List<Object> countParams = new ArrayList<>();

        StringBuilder count=new StringBuilder("SELECT count(0) from dc_tmall_order o left join dc_tmall_order_address a ");
        count.append(" on a.orderId=o.id left join user developer on developer.id = o.developerId ");
        count.append(" WHERE o.shopId=? ");
        countParams.add(reqData.getShopId());

        if (StringUtils.isEmpty(reqData.getOrderId()) == false) {
            sb.append(" AND o.id=? ");
            params.add(reqData.getOrderId());
        }
        if (reqData.getStatus() != null) {
            sb.append(" AND o.status = ? ");
            params.add(reqData.getStatus());
        }
        if(!StringUtils.isEmpty(reqData.getTags()) && reqData.getTags().size()>0){
            sb.append(" AND o.tag REGEXP ?  ");
            StringBuilder tags=new StringBuilder("(");
            reqData.getTags().forEach(tag->tags.append(tag).append("|"));
            String tag=tags.toString().substring(0,tags.toString().length() - 1);
            params.add(tag+")");
        }
        if (StringUtils.isEmpty(reqData.getMobile()) == false) {
            sb.append(" AND a.mobile = ?");
            params.add(reqData.getMobile());
        }
        if (StringUtils.isEmpty(reqData.getStartTime()) == false) {
            sb.append(" AND o.createTime > ?");
            count.append(" AND o.createTime > ?");
            params.add(DateUtil.stampToDateTime(reqData.getStartTime()));
            countParams.add(DateUtil.stampToDateTime(reqData.getStartTime()));
        }
        if (StringUtils.isEmpty(reqData.getEndTime()) == false) {
            sb.append(" AND o.createTime <= ?");
            count.append(" AND o.createTime <= ?");
            params.add(DateUtil.stampToDateTime(reqData.getEndTime()));
            countParams.add(DateUtil.stampToDateTime(reqData.getEndTime()));
        }
        //待确认
        if(!StringUtils.isEmpty(reqData.getAuditStatus()) && reqData.getAuditStatus().intValue()==0){
            sb.append(" and (o.auditStatus=0 and o.status=2 and  IFNULL(o.logisticsCode,'') != 1 ) ");
            count.append(" and (o.auditStatus=0 and o.status=2 and  IFNULL(o.logisticsCode,'') != 1 ) ");
/*            params.add(reqData.getAuditStatus());
            countParams.add(reqData.getAuditStatus());*/
        }
        //待发货
        if(!StringUtils.isEmpty(reqData.getAuditStatus()) && reqData.getAuditStatus().intValue()==1){
            sb.append(" and (o.auditStatus=1 and o.status=2 and o.send_status=4 ) ");
            count.append(" and (o.auditStatus=1 and o.status=2 and o.send_status=4 ) ");
        }
        sb.append(" ORDER BY o.createTime desc LIMIT ?,?");
        params.add((reqData.getPageIndex() - 1) * reqData.getPageSize());
        params.add(reqData.getPageSize());
        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(DcTmallOrderEntity.class), params.toArray(new Object[params.size()]));

       //String countSql = sb.toString().substring(0, sb.toString().indexOf("ORDER")).replace("salesAmount", "IFNULL(count(1),0)");
        int totalSize=jdbcTemplate.queryForObject(count.toString(), Integer.class, countParams.toArray(new Object[countParams.size()]));
        list.forEach(order->order.setItems(jdbcTemplate.query("SELECT * FROM " + Tables.DcTmallOrderItem + " WHERE orderId=?", new BeanPropertyRowMapper<>(DcTmallOrderItemEntity.class), order.getId())));

        return new PagingResponse<>(reqData.getPageIndex(), reqData.getPageSize(), totalSize, list);
    }
    /**
     * 修改订单状态
     * @param orderId
     * @param status
     */
    public void updTaoOrderStatus(Long orderId,Integer status){
        jdbcTemplate.update("update dc_tmall_order set status=? where id=?",status,orderId);
    }

    /**
     * 导出订单
     * @return
     */
//    public List<DcTmallOrderEntity> getListExport(String startTime, String endTime) {
//        List<Object> params = new ArrayList<>();
//        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS o.*,a.contactPerson,mobile,province,city,area,address FROM ").append(Tables.DcTmallOrder).append(" o ");
//        sb.append(" LEFT JOIN ").append(Tables.DcTmallOrderAddress).append(" a on a.orderId=o.id ");
//        sb.append(" WHERE 1=1 ");
//        //下单时间
//        if (!StringUtils.isEmpty(startTime)) {
//            sb.append("AND o.createTime >= ? ");
//            params.add(startTime);
//        }
//        if (!StringUtils.isEmpty(endTime)) {
//            sb.append("AND o.createTime <= ? ");
//            params.add(endTime);
//        }
//
//
//        sb.append(" ORDER BY o.createTime DESC ");
//
//        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(DcTmallOrderEntity.class), params.toArray(new Object[params.size()]));
//
//        if (list != null && list.size() > 0) {
//            //查询item
//            for (var order : list) {
//                order.setItems(jdbcTemplate.query("SELECT * FROM " + Tables.DcTmallOrderItem + " WHERE orderId=?", new BeanPropertyRowMapper<>(DcTmallOrderItemEntity.class), order.getId()));
//            }
//        }
//        return list;
//    }

    /**
     * @param orderId
     * @return
     */
    public DcTmallOrderEntity getOrderDetailAndItemsById(Long orderId) {
        try {
            var orderSQL = "SELECT o.*,addr.contactPerson,addr.mobile,addr.province,addr.city,addr.area,addr.address FROM " + Tables.DcTmallOrder + " as o LEFT JOIN " + Tables.DcTmallOrderAddress + " as addr on addr.orderId = o.id WHERE o.id=?";
            var order = jdbcTemplate.queryForObject(orderSQL, new BeanPropertyRowMapper<>(DcTmallOrderEntity.class), orderId);

            String tmallSQL = "select O.*,(SELECT  concat(color_value,'(',size_value,')') FROM erp_goods_spec WHERE id=O.new_spec_id) newSpec, " +
                    "(SELECT concat(color_value,'(',size_value,')') FROM erp_goods_spec  WHERE specNumber=O.specNumber) erpSpec  " +
                    "from dc_tmall_order_items O where O.orderId=? ";
            var items = jdbcTemplate.query(tmallSQL, new BeanPropertyRowMapper<>(DcTmallOrderItemEntity.class), orderId);
            order.setItems(items);
//            order.setItems(getItemsByOrderId(orderId));

            for (var item : order.getItems()) {
                try {
                    String specNumber = StringUtils.isEmpty(item.getNewSpecNumber()) == false ? item.getNewSpecNumber() : item.getSpecNumber();

                    var erpGoodsSpec = jdbcTemplate.queryForObject("select * from " + Tables.ErpGoodsSpec + " WHERE specNumber=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), specNumber);
                    var sku = jdbcTemplate.queryForObject("select SUM(currentQty) currentQty,SUM(lockedQty) lockedQty from " + Tables.ErpGoodsStockInfo + "  WHERE specId=? AND isDelete = 0", new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), erpGoodsSpec.getId());
                    Long djhCount = jdbcTemplate.queryForObject(" SELECT IFNULL((select SUM(quantity) from erp_order_item WHERE skuId=? AND (STATUS=0 OR STATUS=1) ),0) djhCount", Long.class, erpGoodsSpec.getId());
                    item.setCurrentQty(Optional.ofNullable(sku.getCurrentQty()).orElse(0L));
                    item.setLockedQty(Optional.ofNullable(sku.getLockedQty()).orElse(0L));
                    item.setPickQty(djhCount.longValue());

                } catch (Exception e2) {
                    item.setCurrentQty(0l);
                    item.setLockedQty(0l);
                    item.setPickQty(0l);
                }

            }
            return order;
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 获取订单信息(包含收货地址)
     *
     * @param orderId
     * @return
     */
    public DcTmallOrderEntity getOrderEntityById(Long orderId) {
        try {
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT o.*,addr.contactPerson,addr.mobile,addr.province,addr.city,addr.area,addr.address ");
            sb.append(" FROM ").append(Tables.DcTmallOrder).append(" as o ");
            sb.append(" LEFT JOIN ").append(Tables.DcTmallOrderAddress).append(" as addr on addr.orderId = o.id ");
            sb.append(" WHERE o.id=? ");
//            var orderSQL = "SELECT o.*,addr.contactPerson,addr.mobile,addr.province,addr.city,addr.area,addr.address FROM " + Tables.DcTmallOrder + " as o LEFT JOIN " + Tables.DcTmallOrderAddress + " as addr on addr.orderId = o.id WHERE o.id=?";

            return jdbcTemplate.queryForObject(sb.toString(), new BeanPropertyRowMapper<>(DcTmallOrderEntity.class), orderId);
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * @param orderId
     * @return
     */
    public List<DcTmallOrderItemEntity> getOrderItemsByOrderId(Long orderId) {
        return jdbcTemplate.query("SELECT * FROM " + Tables.DcTmallOrderItem + " WHERE orderId=?", new BeanPropertyRowMapper<>(DcTmallOrderItemEntity.class), orderId);
    }


    /**
     * 修改商品最新规格
     *
     * @param itemId
     * @param specId
     */
    @Transactional
    public Integer updGoodTmallSpec(Integer itemId, Integer specId) {

        try {
            /************查询规格信息*************/
            var item = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.DcTmallOrderItem + " WHERE id=?", new BeanPropertyRowMapper<>(DcTmallOrderItemEntity.class), itemId);

            var spec = jdbcTemplate.queryForObject("select * from " + Tables.ErpGoodsSpec + " where id=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), specId);
            if (StringUtils.isEmpty(item.getSpecNumber())) {
                //如果原本就没有SKU，那么就补充原SKU字段
                return jdbcTemplate.update("update " + Tables.DcTmallOrderItem + " set skuId=?,specNumber=?,productId=? where id=?", specId, spec.getSpecNumber(), spec.getGoodsId(), itemId);

            } else {
                //原来有SKU，那么修改新SKU字段
                return jdbcTemplate.update("update " + Tables.DcTmallOrderItem + " set new_spec_id=?,new_spec_number=? where id=?", specId, spec.getSpecNumber(), itemId);
            }


        } catch (Exception e) {
            return -1;
        }

    }

    /**
     * 添加采购订单退货商品信息
     *
     * @param orderId
     * @param itemIds
     * @param nums
     */
//    @Transactional(rollbackFor = Exception.class)
//    public ResultVo<Integer> addTaoBaoOrderCancel(Long orderId, String[] itemIds, String[] nums) {
//        try {
//            var order = jdbcTemplate.queryForObject("select status from " + Tables.DcTmallOrder + " where id=?", new BeanPropertyRowMapper<>(DcTmallOrderEntity.class), orderId);
//            if (order.getStatus().intValue() < EnumTaoBaoOrderStatus.send_goods.getIndex()) {
//                return new ResultVo<>(EnumResultVo.ParamsError, "订单已发货才能申请退货");
//            }
//            String addOrderCancelSQL = "INSERT INTO " + Tables.OrderCancel + " SET order_cancel_num=?,order_id=?,user_id=?,total_amount=0,create_on=unix_timestamp(now()),state=1 ";
//            KeyHolder keyHolder = new GeneratedKeyHolder();
//            jdbcTemplate.update(new PreparedStatementCreator() {
//                @Override
//                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
//                    PreparedStatement ps = connection.prepareStatement(addOrderCancelSQL, Statement.RETURN_GENERATED_KEYS);
//                    ps.setString(1, StringUtil.createCancelOrderNum());
//                    ps.setLong(2, orderId);
//                    ps.setInt(3, 0);
//                    return ps;
//                }
//            }, keyHolder);
//            Long id = keyHolder.getKey().longValue();
//            Double totalPrice = 0d;
//            Integer count = 0;
//            for (int i = 0, n = itemIds.length; i < n; i++) {
//                var item = jdbcTemplate.queryForObject("select * from " + Tables.DcTmallOrderItem + " where id=?", new BeanPropertyRowMapper<>(DcTmallOrderItemEntity.class), itemIds[i]);
//                if (item.getAfterSaleState() == 0) {
//                    count += 1;
//                    Integer number = Integer.parseInt(nums[i]) > item.getQuantity().intValue() ? item.getQuantity().intValue() : Integer.parseInt(nums[i]);

//                    //添加退货明细
//                    String addOrderCancelItemSQL = "insert into order_cancel_item set order_cancel_id=?,order_id=?,order_item_id=?,goods_id=?,title=?,image=?,spec_id=?,spec_number=?,color=?,size=?,price=?,quantity=?,status=1";
//                    String specNum = item.getSpecNumber();
//                    if (StringUtils.isEmpty(item.getNewSpecNumber()) == false) {
//                        //如果改过sku，用新sku退货
//                        specNum = item.getNewSpecNumber();
//                    }
//                    jdbcTemplate.update(addOrderCancelItemSQL, id, item.getOrderId(), item.getId(), 0, item.getGoodsTitle(), item.getProductImgUrl(), 0, specNum
//                            , item.getSkuInfo().length() > 0 ? item.getSkuInfo().substring(0, item.getSkuInfo().indexOf(";")) : "",
//                            item.getSkuInfo().length() > 0 ? item.getSkuInfo().substring(item.getSkuInfo().lastIndexOf(":") + 1) : "",
//                            item.getPrice(), number);

//                    totalPrice += item.getPrice().doubleValue() * number;
//                    jdbcTemplate.update("update " + Tables.DcTmallOrderItem + " set after_sale_state=1 where id=?", item.getId());
//                }
//            }
//            if (count > 0)
//                jdbcTemplate.update("update " + Tables.OrderCancel + " set total_amount=? where id=? ", totalPrice, id);
//            if (count == 0) jdbcTemplate.update("delete  from " + Tables.OrderCancel + "  where id=?", id);

//            //TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();

//            return new ResultVo<>(EnumResultVo.SUCCESS, "申请成功");
//        } catch (Exception e) {
//            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//            return new ResultVo<>(EnumResultVo.Fail, e.getMessage());
//        }
//    }

@Transactional(rollbackFor = Exception.class)
   public ResultVo<Long> addTaoOrderRefund(Long orderItemId, String refundId) {
    var orderItem = jdbcTemplate.queryForObject("SELECT oi.*,o.shopId FROM "+Tables.DcTmallOrderItem+" as oi left join dc_tmall_order o on o.id=oi.orderId WHERE oi.id=?",new BeanPropertyRowMapper<>(DcTmallOrderItemEntity.class),orderItemId );

    if(orderItem.getRefundStatus().intValue() != 0) return new ResultVo<>(EnumResultVo.StateError, "该item已经有售后了");

       try {
        //退款状态。可选值WAIT_SELLER_AGREE(买家已经申请退款，等待卖家同意) WAIT_BUYER_RETURN_GOODS(卖家已经同意退款，等待买家退货) 
        //WAIT_SELLER_CONFIRM_GOODS(买家已经退货，等待卖家确认收货) SELLER_REFUSE_BUYER(卖家拒绝退款) CLOSED(退款关闭) SUCCESS(退款成功)
           String addOrderCancelSQL = "INSERT INTO " + Tables.DcTmallOrderRefund + " SET refund_id=?,tid=?,oid=?,refund_fee=?"+
            ",num=?,status=1,createOn=unix_timestamp(now()),shopId=?,erpGoodsId=?,erpGoodsSpecId=?,specNumber=?,after_sales_type=? ";
           KeyHolder keyHolder = new GeneratedKeyHolder();
           jdbcTemplate.update(new PreparedStatementCreator() {
               @Override
               public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                   PreparedStatement ps = connection.prepareStatement(addOrderCancelSQL, Statement.RETURN_GENERATED_KEYS);
                   ps.setString(1, refundId);
                   ps.setLong(2, Long.parseLong(orderItem.getOrderId()));
                   ps.setLong(3, Long.parseLong(orderItem.getSubItemId()));
                   ps.setBigDecimal(4, orderItem.getItemAmount());
                   ps.setInt(5, orderItem.getQuantity().intValue());
                   ps.setInt(6,orderItem.getShopId() );
                   ps.setInt(7, orderItem.getErpGoodsId());
                   ps.setInt(8, orderItem.getErpGoodsSpecId());
                   ps.setString(9, orderItem.getSpecNumber());
                   ps.setInt(10, 1);
                   return ps;
               }
           }, keyHolder);

           Long id = keyHolder.getKey().longValue();
           jdbcTemplate.update("UPDATE "+Tables.DcTmallOrderItem +" SET refundStatus=1,refundStatusStr='REFUNDING',refundAmount=itemAmount,refundId=? WHERE id=?",refundId,orderItemId);

           return new ResultVo<>(EnumResultVo.SUCCESS, "申请成功",id);
       } catch (Exception e) {
           TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
           return new ResultVo<>(EnumResultVo.Fail, e.getMessage());
       }
   }

    /**
     * 查询天猫退货列表
     *
     * @param pageIndex
     * @param pageSize
     * @param orderNum
     * @param startTime
     * @param endTime
     * @param returnOrderNum
     * @param buyerName
     * @param state
     * @return
     */
    public PagingResponse<RefundOrderListVo> getTmallAfterOrders(Integer pageIndex, Integer pageSize, String orderNum, Integer startTime, Integer endTime, String returnOrderNum, String buyerName, Integer state, Integer orderSource) {
        StringBuilder builder = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS C.*,D.id order_num,D.buyerName mj_mobile FROM " + Tables.OrderCancel + " C," + Tables.DcTmallOrder + " D WHERE C.order_id=D.id AND orderSource=? ");
        List<Object> params = new ArrayList<>();
        params.add(orderSource);
        if (StringUtils.isEmpty(orderNum) == false) {
            builder.append(" and D.id like ? ");
            params.add("%" + orderNum + "%");
        }
        if (startTime > 0) {
            builder.append("AND C.create_on>=? ");
            params.add(startTime);
        }
        if (endTime > 0) {
            builder.append("AND C.create_on<? ");
            params.add(endTime);
        }
        if (StringUtils.isEmpty(returnOrderNum) == false) {
            builder.append(" and C.order_cancel_num like ? ");
            params.add("%" + returnOrderNum + "%");
        }
        if (!StringUtils.isEmpty(buyerName)) {
            builder.append("AND D.buyerName LIKE ? ");
            params.add("%" + buyerName + "%");
        }
        if (state > -1) {
            builder.append(" AND C.state=? ");
            params.add(state);
        }
        builder.append(" ORDER BY C.id DESC LIMIT ?,? ");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(builder.toString(), new BeanPropertyRowMapper<>(RefundOrderListVo.class), params.toArray());
        list.forEach(item -> {
            var items = jdbcTemplate.query("SELECT * FROM " + Tables.OrderCancelItem + " WHERE order_cancel_id=? ", new BeanPropertyRowMapper<>(OrderCancelItemEntity.class), item.getId());
            item.setItems(items);
            item.setTotalCount(jdbcTemplate.queryForObject("SELECT SUM(quantity) FROM order_cancel_item WHERE order_cancel_id=? AND order_id=?", Long.class, item.getId(), item.getOrderId()));

        });
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    /**
     * 天猫订单退货
     *
     * @param id
     * @param state
     * @param exress
     * @return
     */
//    @Transactional(rollbackFor = Exception.class)
//    public ResultVo<Integer> reviewRefundTmall(Long id, Integer state, ExpressInfoVo exress) {
//        //查询退款单
//        String sql = "SELECT * FROM " + Tables.OrderCancel + " WHERE id=?";
//        OrderCancelEntity refundOrder = null;
//        try {
//            refundOrder = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(OrderCancelEntity.class), id);
//        } catch (Exception e1) {
//            return new ResultVo<>(EnumResultVo.NotFound, "退货订单不存在");//订单不存在return 404;
//        }
//        DcTmallOrderEntity order = null;
//        try {
//            //查询关联的订单
//            order = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.DcTmallOrder + " WHERE id=?", new BeanPropertyRowMapper<>(DcTmallOrderEntity.class), refundOrder.getOrderId());
//        } catch (Exception e) {
//            return new ResultVo<>(EnumResultVo.NotFound, "订单不存在");//订单不存在return 404;
//        }
//        //订单来源
//        String orderSource = ErpOrderSourceEnum.TMALL.getIndex();
//        var returnOrderItem = jdbcTemplate.query("SELECT * FROM " + Tables.OrderCancelItem + " WHERE order_cancel_id=?", new BeanPropertyRowMapper<>(OrderCancelItemEntity.class), refundOrder.getId());
//        if (returnOrderItem == null || returnOrderItem.size() == 0)
//            return new ResultVo<>(EnumResultVo.NotFound, "退货订单明细不存在");
//        /**1.同意退货**/
//        if (state.intValue() == OrderCancelStateEnums.Delivered.getIndex() && refundOrder.getState() == OrderCancelStateEnums.RefundApply.getIndex()) {
//
//            //更新退货订单信息 order_cancel
//            jdbcTemplate.update("UPDATE " + Tables.OrderCancel + " SET send_company=?,send_company_code=?,send_code=?,state=? WHERE id =?", exress.getLogisticsCompany(), exress.getLogisticsCompanyCode(), exress.getLogisticsCode(), state, id);
//
//            jdbcTemplate.update("UPDATE " + Tables.OrderCancelItem + " SET status=? WHERE order_cancel_id =?", state, refundOrder.getId());
//
//            /**********循环更新退货商品信息 ***********/
//            for (var ritem : returnOrderItem) {
//                //更新退货商品信息
//                jdbcTemplate.update("UPDATE " + Tables.OrderItem + " SET after_sale_state=? WHERE id =?", state, ritem.getOrderItemId());
//            }
//
//            /***********  插入仓库系统退款订单 erp_order_return    erp_order_return_item  *************/
//            try {
//                //插入仓库退货订单 erp_order_return
//                String returnOrder = "INSERT INTO " + Tables.ErpOrderReturn +
//                        " (order_num,orderTime,createTime,contactPerson,mobile,address,source,status,logisticsCompany,logisticsCompanyCode,logisticsCode,source_order_num)" +
//                        " VALUE (?,?,?,?,?,?,?,?,?,?,?,?)";
//
//                KeyHolder keyHolder = new GeneratedKeyHolder();
//
//                DcTmallOrderEntity finalOrder = order;
//
//                OrderCancelEntity finalRefundOrder = refundOrder;
//                String finalOrderSource = orderSource;
//                jdbcTemplate.update(new PreparedStatementCreator() {
//                    @Override
//                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
//                        PreparedStatement ps = connection.prepareStatement(returnOrder, Statement.RETURN_GENERATED_KEYS);
//                        ps.setString(1, finalRefundOrder.getOrderCancelNum());
//                        ps.setLong(2, finalRefundOrder.getCreateOn());
//                        ps.setLong(3, System.currentTimeMillis() / 1000);
//                        ps.setString(4, finalOrder.getBuyerName());
//                        ps.setString(5, Optional.ofNullable(finalOrder.getMobile()).orElse(""));
//                        ps.setString(6, exress != null ? exress.getAddress() : finalOrder.getAddress());
//                        ps.setString(7, finalOrderSource);
//                        ps.setInt(8, EnumOrderReturnStatus.WaitReceive.getIndex());
//                        ps.setString(9, exress != null ? exress.getLogisticsCompany() : "");
//                        ps.setString(10, exress != null ? exress.getLogisticsCompanyCode() : "");
//                        ps.setString(11, exress != null ? exress.getLogisticsCode() : "");
//                        ps.setString(12, finalOrder.getId().toString());
//                        return ps;
//                    }
//                }, keyHolder);
//                Long returnOrderId = keyHolder.getKey().longValue();
//
//                //插入仓库退货订单明细 erp_order_return_item
//                String returnOrderItemSQL = "INSERT INTO " + Tables.ErpOrderReturnItem + " (orderId,goodsId,skuId,skuNumber,quantity,receiveType,createTime,status,inQuantity,badQuantity) VALUE (?,?,?,?,?,?,?,?,?,?)";
//
//                for (var ritem : returnOrderItem) {
//                    //查询仓库系统sku
//                    var sku = jdbcTemplate.queryForObject("select * from " + Tables.ErpGoodsSpec + " WHERE specNumber=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), ritem.getSpecNumber());
//                    //更新退货商品信息
//                    jdbcTemplate.update(returnOrderItemSQL, returnOrderId, sku.getGoodsId(), sku.getId(), sku.getSpecNumber(), ritem.getQuantity(), 0, System.currentTimeMillis() / 1000, 0, 0, 0);
//                }
//            } catch (Exception e) {
//                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//                return new ResultVo<>(EnumResultVo.Fail, "同意退货处理失败");
//            }
//        }
//
//        /**2.拒绝退货**/
//        if (state.intValue() == OrderCancelStateEnums.Refuse.getIndex() && refundOrder.getState() == OrderCancelStateEnums.RefundApply.getIndex()) {
//            //拒绝退货
//            if (refundOrder.getType() == 0) {
//                //更新退货订单信息 order_cancel
//                jdbcTemplate.update("UPDATE " + Tables.OrderCancel + " SET state=? WHERE id =?", state, id);
//
//                jdbcTemplate.update("UPDATE " + Tables.OrderCancelItem + " SET status=? WHERE order_cancel_id =?", state, refundOrder.getId());
//                /**********循环更新退货商品信息 ***********/
//                for (var ritem : returnOrderItem) {
//                    //更新退货商品信息
//                    jdbcTemplate.update("UPDATE " + Tables.OrderItem + " SET after_sale_state=? WHERE id =?", state, ritem.getOrderItemId());
//                }
//
//            } else if (refundOrder.getType() == 1) {
//                //更新退货订单信息
//                jdbcTemplate.update("UPDATE " + Tables.OrderCancel + " SET state=? WHERE id =?", state, id);
//                //更新订单订单待发货
//                jdbcTemplate.update("UPDATE " + Tables.Order + " SET state=? WHERE id =?", 2, refundOrder.getOrderId());
//            }
//
//        }
//        return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
//    }

    /**
     * tmall订单关联业务员
     *
     * @return
     */
//    @Transactional(rollbackFor = Exception.class)
//    public ResultVo<Integer> addTmallOrderByDeveLoper(DcTmallOrderEntity order, Integer buyUserId, Integer orderType, ErpOrderSourceEnum sourceEnum) {
//
//        var orders = jdbcTemplate.query("select id from " + Tables.Order + " where order_num=?", new BeanPropertyRowMapper<>(OrdersEntity.class), order.getId());
//        if (null != orders && orders.size() > 0) return new ResultVo<>(EnumResultVo.SUCCESS, "订单已关联业务员");
//
//        /*****1、添加order*****/
//        StringBuilder orderInsertSQL = new StringBuilder();
//        orderInsertSQL.append("INSERT INTO ").append(Tables.Order);
//        orderInsertSQL.append(" SET ");
//        orderInsertSQL.append(" order_num=?,");
//        orderInsertSQL.append(" buyer_name=?,");
//        orderInsertSQL.append(" user_id=?,");
//        orderInsertSQL.append(" goods_total_price=?,");
//        orderInsertSQL.append(" freight=?,");
//        orderInsertSQL.append(" order_total_price=?,");
//        orderInsertSQL.append(" payment_price=?,");
//        orderInsertSQL.append(" create_on=?,");
//        orderInsertSQL.append(" state=?,");
//        orderInsertSQL.append(" type=?,");
//        orderInsertSQL.append(" goods_num=0,goods_unit_num=0,auditStatus=1,buyer_order_type=1,");
//        orderInsertSQL.append(" address=?,");
//        orderInsertSQL.append(" consignee=?,");
//        orderInsertSQL.append(" consignee_mobile=?,");
//        orderInsertSQL.append(" payment_state=?,");
//        orderInsertSQL.append(" payment_method=?, ");
//        orderInsertSQL.append(" source=?");
//        try {
//            jdbcTemplate.update(orderInsertSQL.toString(),
//                    String.valueOf(order.getId()),
//                    order.getBuyerName(),
//                    buyUserId,
//                    order.getTotalAmount(),
//                    order.getShippingFee(),
//                    order.getTotalAmount(),
//                    order.getTotalAmount(),
//                    DateUtil.dateToStamp(order.getCreateTime()),
//                    OrderStateEnums.Completed.getIndex(),
//                    orderType,
//                    Optional.ofNullable(order.getAddress()).orElse(""),
//                    order.getContactPerson(),
//                    order.getMobile(),
//                    OrderPaymentStateEnums.Success.getIndex(),
//                    OrderPaymentMethodEnums.Third_PAY.getIndex(),
//                    sourceEnum.getIndex()
//            );
//            Integer count = 0;
//            for (var item : order.getItems()) count += item.getQuantity().intValue();
//
//            Integer deveLoperId = jdbcTemplate.queryForObject("SELECT IFNULL((SELECT developer_id  from " + Tables.User + " where id=?),0) id", Integer.class, buyUserId);
//            jdbcTemplate.update("update " + Tables.Order + " set goods_num=?,goods_unit_num=?,developer_id=? where order_num=?", count, count, deveLoperId, String.valueOf(order.getId()));
//        } catch (Exception ex) {
//            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//            return new ResultVo<>(EnumResultVo.Fail, "TMALL订单" + order.getId() + "关联客户失败：" + ex);
//        }
//        return new ResultVo<>(EnumResultVo.SUCCESS, "关联业务员成功");
//    }
    public void batchUpdateTmallOrderReceiver(List<DcTmallOrderEntity> orderList) {
        for (var order : orderList) {
            jdbcTemplate.update("UPDATE " + Tables.DcTmallOrderAddress + " SET contactPerson=?,mobile=?,address = ? WHERE orderId=?", order.getContactPerson(), order.getMobile(), order.getAddress(), order.getId());
        }
    }


    private StringBuilder getFinanceOrderItemListSQL() {
        StringBuilder sb = new StringBuilder();

        sb.append("SELECT SQL_CALC_FOUND_ROWS o.logisticsCompany,o.logisticsCode,o.status as taoStatus");
        sb.append(",oi.orderId as orderNum,oi.goodsTitle as goodsName,oi.goodsNumber,oi.skuInfo as specName,oi.specNumber,oi.quantity,oi.itemAmount");
        sb.append(",o.createTime,o.send_status,sh.name as shopName,o.shopId ");
        sb.append(",o.buyerName as buyerNick");//客户名
        sb.append(",oa.contactPerson as receiver");//收货人
        sb.append(" FROM ").append(Tables.DcTmallOrderItem).append(" as oi ");
        sb.append(" LEFT JOIN ").append(Tables.DcTmallOrder).append(" as o on o.id=oi.orderId");
        sb.append(" LEFT JOIN ").append(Tables.DcShop).append(" as sh on sh.id = o.shopId ");
        sb.append(" LEFT JOIN ").append(Tables.DcTmallOrderAddress).append(" as oa on oa.orderId = o.id ");
        sb.append(" WHERE 1=1 ");
        return sb;
    }

    /**
     * 查询订单明细list （供财务使用）
     *
     * @param pageIndex
     * @param pageSize
     * @param orderId
     * @param receiverMobile
     * @param startTime
     * @param endTime
     * @return
     */
    @Transactional
    public PagingResponse<FinanceOrderItemListVo> getOrderItemList(Integer pageIndex, Integer pageSize, String orderId, Integer sendStatus, String receiverMobile,String specNumber, String startTime, String endTime) {
        StringBuilder sb = getFinanceOrderItemListSQL();

        List<Object> params = new ArrayList<>();
//        sb.append(" AND (o.status=? OR o.status=?)");
//        params.add(EnumTmallOrderStatus.WAIT_BUYER_CONFIRM_GOODS.getStatus());//等待买家确认收货（也就是已发货的）
//        params.add(EnumTmallOrderStatus.TRADE_FINISHED.getStatus());//交易成功

        if (!StringUtils.isEmpty(orderId)) {
            sb.append("AND oi.orderId = ? ");
            params.add(orderId);
        }
        if (!StringUtils.isEmpty(specNumber)) {
            sb.append("AND oi.specNumber = ? ");
            params.add(specNumber);
        }
        //收货人手机号
//        if (!StringUtils.isEmpty(mobile)) {
//            sb.append("AND C.mobile = ? ");
//            params.add(mobile);
//        }

        if (!StringUtils.isEmpty(startTime)) {
            sb.append("AND o.createTime > ? ");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime)) {
            sb.append("AND o.createTime <= ? ");
            params.add(endTime);
        }

        if (sendStatus != null && sendStatus > -1) {
            sb.append("AND  o.send_status = ?  ");
            params.add(sendStatus);
        }

        sb.append("ORDER BY o.createTime DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<FinanceOrderItemListVo> lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(FinanceOrderItemListVo.class), params.toArray(new Object[params.size()]));

        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), lists);
    }

    public List<FinanceOrderItemListVo> getOrderItemListForExcel(String orderId, Integer sendStatus, String receiverMobile,String specNumber, String startTime, String endTime) {
        StringBuilder sb = getFinanceOrderItemListSQL();

        List<Object> params = new ArrayList<>();

//        sb.append(" AND (o.status=? OR o.status=?)");
//        params.add(EnumTmallOrderStatus.WAIT_BUYER_CONFIRM_GOODS.getStatus());//等待买家确认收货（也就是已发货的）
//        params.add(EnumTmallOrderStatus.TRADE_FINISHED.getStatus());//交易成功

        if (!StringUtils.isEmpty(orderId)) {
            sb.append("AND oi.orderId = ? ");
            params.add(orderId);
        }

        //收货人手机号
        if (!StringUtils.isEmpty(specNumber)) {
            sb.append("AND oi.specNumber = ? ");
            params.add(specNumber);
        }

        if (!StringUtils.isEmpty(startTime)) {
            sb.append("AND o.createTime > ? ");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime)) {
            sb.append("AND o.createTime <= ? ");
            params.add(endTime);
        }

        if (sendStatus != null && sendStatus > -1) {
            sb.append("AND  o.send_status = ?  ");
            params.add(sendStatus);
        }

        sb.append("ORDER BY o.createTime DESC ");


        List<FinanceOrderItemListVo> lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(FinanceOrderItemListVo.class), params.toArray(new Object[params.size()]));
        return lists;
    }

    private StringBuilder getFinanceOrderListSQL() {
        StringBuilder sb = new StringBuilder();

        sb.append("SELECT SQL_CALC_FOUND_ROWS o.id as order_num,o.logisticsCompany,o.logisticsCode,o.status as taoStatus,o.payTime as payDateTime,o.send_time");
        sb.append(",o.buyerName as buyerNick,o.totalAmount,o.shippingFee");
        sb.append(",o.createTime,o.send_status,sh.name as shopName,o.shopId,o.sellerMemo ");
        sb.append(",(SELECT SUM(quantity) FROM " + Tables.DcTmallOrderItem + " WHERE orderId=o.id) as totalQuantity");
        sb.append(",o.buyerName as buyerNick");//客户名
        sb.append(",IFNULL(dev.user_name,dev.nick_name) as developer");
        sb.append(",oa.contactPerson as receiver,oa.mobile as receiverMobile,oa.address as receiverAddress");//收货人
        sb.append(" FROM ").append(Tables.DcTmallOrder).append(" as o ");
        sb.append(" LEFT JOIN ").append(Tables.DcShop).append(" as sh on sh.id = o.shopId ");
        sb.append(" LEFT JOIN ").append(Tables.DcTmallOrderAddress).append(" as oa on oa.orderId = o.id ");
        sb.append(" LEFT JOIN ").append(Tables.User).append(" as dev on dev.id = o.developerId ");
        sb.append(" WHERE 1=1 ");
        return sb;
    }

    @Transactional
    public PagingResponse<FinanceOrderListVo> getOrderList(Integer pageIndex, Integer pageSize, String orderId, Integer sendStatus, String receiverMobile, Integer developerId, String startTime, String endTime) {
        StringBuilder sb = getFinanceOrderListSQL();

        List<Object> params = new ArrayList<>();

        if (!StringUtils.isEmpty(orderId)) {
            sb.append("AND o.id = ? ");
            params.add(orderId);
        }

        //收货人手机号
        if (!StringUtils.isEmpty(receiverMobile)) {
            sb.append("AND oa.mobile = ? ");
            params.add(receiverMobile);
        }


        if (!StringUtils.isEmpty(startTime)) {
            sb.append("AND o.createTime > ? ");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime)) {
            sb.append("AND o.createTime <= ? ");
            params.add(endTime);
        }

        if (sendStatus != null && sendStatus > -1) {
            sb.append("AND  o.send_status = ?  ");
            params.add(sendStatus);
        }
        if (developerId != null && developerId > 0) {
            sb.append(" AND o.developerId = ? ");
            params.add(developerId);
        }

        sb.append("ORDER BY o.createTime DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<FinanceOrderListVo> lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(FinanceOrderListVo.class), params.toArray(new Object[params.size()]));

        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), lists);
    }

    @Transactional
    public List<FinanceOrderListVo> getOrderListForExcel(String orderId, Integer sendStatus, String receiverMobile, Integer developerId, String startTime, String endTime) {
        StringBuilder sb = getFinanceOrderListSQL();

        List<Object> params = new ArrayList<>();

        if (!StringUtils.isEmpty(orderId)) {
            sb.append("AND o.id = ? ");
            params.add(orderId);
        }

        //收货人手机号
        if (!StringUtils.isEmpty(receiverMobile)) {
            sb.append("AND oa.mobile = ? ");
            params.add(receiverMobile);
        }


        if (!StringUtils.isEmpty(startTime)) {
            sb.append("AND o.createTime > ? ");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime)) {
            sb.append("AND o.createTime <= ? ");
            params.add(endTime);
        }

        if (sendStatus != null && sendStatus > -1) {
            sb.append("AND  o.send_status = ?  ");
            params.add(sendStatus);
        }
        if (developerId != null && developerId > 0) {
            sb.append(" AND o.developerId = ? ");
            params.add(developerId);
        }

        sb.append("ORDER BY o.createTime DESC ");


        List<FinanceOrderListVo> lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(FinanceOrderListVo.class), params.toArray(new Object[params.size()]));

        return lists;
    }



    /**
     * 修改订单商品
     * @param orderItemId
     * @param erpGoodSpecId
     * @param quantity
     * @return
     */
    public ResultVo<Long> updOrderSpec(Long orderItemId, Integer erpGoodSpecId, Integer quantity){
        if(orderItemId==null || orderItemId.longValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少orderItemId");
        }

        if(erpGoodSpecId==null || erpGoodSpecId.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少erpGoodSpecId");
        }
        if(quantity==null || quantity.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少quantity");
        }

        String sql = "SELECT * FROM "+Tables.DcTmallOrder+" WHERE id = (SELECT orderId FROM "+Tables.DcTmallOrderItem+" WHERE id=? )";
        try {
            var order = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(DcTmallOrderEntity.class),orderItemId);
            if(order.getAuditStatus().intValue()!=0)
                return new ResultVo<>(EnumResultVo.DataError,"订单已经确认，不允许修改");

            if(order.getSendStatus().intValue() != 0 )
                return new ResultVo<>(EnumResultVo.DataError,"订单不是待发货状态，不允许修改");

            var orderItem = jdbcTemplate.queryForObject("SELECT * FROM "+Tables.DcTmallOrderItem+" WHERE id=?",new BeanPropertyRowMapper<>(DcTmallOrderItemEntity.class),orderItemId);

            /**************************/
            String erpGoodSpecSQL = "SELECT s.*,g.name as goodTitle,g.number as goodsNumber from "+Tables.ErpGoodsSpec+" s LEFT JOIN " + Tables.ErpGoods + " g ON g.id=s.goodsId WHERE s.id=?";
            var erpOrderSpec = jdbcTemplate.queryForObject(erpGoodSpecSQL,new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),erpGoodSpecId);

            String remark = "修改商品，原sku："+orderItem.getErpGoodsSpecId()+"("+orderItem.getSpecNumber()+")数量:"+orderItem.getQuantity()+"，新sku:"+erpOrderSpec.getId()+"("+erpOrderSpec.getSpecNumber()+")数量："+quantity;

            jdbcTemplate.update("UPDATE "+Tables.DcTmallOrderItem+" SET erpGoodsId=?,erpGoodsSpecId=?,goodsNumber=?,specNumber=?,quantity=?,remark=? WHERE id=?"
                    ,erpOrderSpec.getGoodsId(),erpOrderSpec.getId(),erpOrderSpec.getGoodsNumber(),erpOrderSpec.getSpecNumber(),quantity,remark,orderItemId);

            return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，不存在orderItem");
        }
    }
    /**
     * 修改订单是否为刷单
     */
    public void updOderTag(UpdOrderTagReq req){
        StringBuilder builder=new StringBuilder();
        for(Integer tag : req.getTags()){
            builder.append(tag).append(",");
        }
        jdbcTemplate.update("update dc_tmall_order set tag=?,remark=? where id=?",builder.toString(),req.getRemark(),req.getId());
    }
    /**
     * 订单成本修改新增
     * @param costs
     */
    public void  updOrderTagCost(List<DcTmallOrderCostEntity> costs){
        String SQL=" dc_tmall_order_cost set dc_tmall_order_id=?,tmall_order_tag_id=?,amount=? ";
        costs.forEach(cost->{
            if(cost.getId()>0L){
                jdbcTemplate.update("update " + SQL +" where id=?",cost.getDcTmallOrderId(),cost.getTmallOrderTagId(),cost.getAmount(),cost.getId());
            }else jdbcTemplate.update("insert into"+ SQL,cost.getDcTmallOrderId(),cost.getTmallOrderTagId(),cost.getAmount());
        });
    }
    /**
     * 成本列表
     * @param query
     * @return
     */
    public PagingResponse<DcTmallOrderCostEntity> orderCosts(TmallOrderCostQuery query){
        StringBuilder builder = new StringBuilder("select c.*,t.tag_name from dc_tmall_order_cost c left join dc_tmall_order_tag t on c.tmall_order_tag_id=t.id where c.dc_tmall_order_id=? ");
        List<Object> params = new ArrayList<>();
        params.add(query.getOrderId());
/*        builder.append(" ORDER BY C.id DESC LIMIT ?,? ");
        params.add((query.getPageIndex() - 1) * query.getPageSize());
        params.add(query.getPageSize());*/
        var list = jdbcTemplate.query(builder.toString(), new BeanPropertyRowMapper<>(DcTmallOrderCostEntity.class), params.toArray());
        return new PagingResponse<>(query.getPageIndex(), query.getPageSize(), getTotalSize(), list);
    }
    /**
     * 导入成本列表
     * @param costList
     */
    public void batchUpdateTmallOrderCost(List<DcTmallOrderCostEntity> costList) {
        for (var order : costList) {
            if(order.getAmount()==0d)continue;
            Integer count= jdbcTemplate.queryForObject("select count(0) from dc_tmall_order_cost where id=? and tmall_order_tag_id=5",Integer.class,order.getId());
            if(count.intValue()>0)continue;
            jdbcTemplate.update("insert dc_tmall_order_cost SET dc_tmall_order_id=?,tmall_order_tag_id=5,amount =?,remark=? ",order.getDcTmallOrderId(),order.getAmount(),order.getRemark());
        }
    }

    /**
     * 添加口罩刷单
     * @param salesOrder
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> addHySalesOrder(ErpSalesOrderDetailVo salesOrder){
        Long salesOrderId = 0L;
        StringBuilder insertSQL = new StringBuilder();
        insertSQL.append("INSERT INTO ").append("hy_sales_order");
        insertSQL.append(" set  orderNum=?,");
        insertSQL.append(" goodsTotalAmount=?,");
        insertSQL.append(" shippingFee=?,");
        insertSQL.append(" totalAmount=?,");
        insertSQL.append(" contactPerson=?,");
        insertSQL.append(" contactMobile=?,");
        insertSQL.append(" province=?,");
        insertSQL.append(" city=?,");
        insertSQL.append(" area=?,");
        insertSQL.append(" address=?,");
        insertSQL.append(" saleType=?,");
        insertSQL.append(" status=?,");
        insertSQL.append(" createBy=?,");
        insertSQL.append(" source=?,");
        insertSQL.append(" payMethod=?,");
        insertSQL.append(" payStatus=?,");
        insertSQL.append(" payTime=?,");
        insertSQL.append(" payAmount=?,");
        insertSQL.append(" orderDate=?,");
        insertSQL.append(" createOn=?, ");
        insertSQL.append(" sellerMemo=?, ");
        insertSQL.append(" salesManId=? ");
        try {
                KeyHolder keyHolder = new GeneratedKeyHolder();
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(insertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1, salesOrder.getOrderNum());
                        ps.setBigDecimal(2, salesOrder.getGoodsTotalAmount());
                        ps.setBigDecimal(3, salesOrder.getShippingFee());
                        ps.setBigDecimal(4, salesOrder.getTotalAmount());
                        ps.setString(5, salesOrder.getContactPerson());
                        ps.setString(6, salesOrder.getContactMobile());
                        ps.setString(7, salesOrder.getProvince());
                        ps.setString(8, salesOrder.getCity());
                        ps.setString(9, salesOrder.getArea());
                        ps.setString(10, salesOrder.getAddress());
                        ps.setInt(11, salesOrder.getSaleType());
                        ps.setInt(12, salesOrder.getStatus());
                        ps.setString(13, salesOrder.getCreateBy());
                        ps.setString(14, salesOrder.getSource());
                        ps.setInt(15, salesOrder.getPayMethod());
                        ps.setInt(16, salesOrder.getPayStatus());
                        ps.setLong(17, salesOrder.getPayTime());
                        ps.setBigDecimal(18, salesOrder.getPayAmount());
                        ps.setString(19, salesOrder.getOrderDate());
                        ps.setLong(20, System.currentTimeMillis() / 1000);
                        ps.setString(21, salesOrder.getSellerMemo());
                        ps.setInt(22, 10000000);//salesOrder.getSalesManId()
                        return ps;
                    }
                }, keyHolder);
                salesOrderId = keyHolder.getKey().longValue();

            //添加订单明细
            String itemSQL = "INSERT INTO hy_sales_order_item (orderId,itemAmount,goodsId,goodsTitle,goodsNumber,goodsImage,skuInfo,specId,specNumber,price,quantity) VALUE (?,?,?,?,?,?,?,?,?,?,?)";

            for (ErpSalesOrderItemEntity item : salesOrder.getItems()) {
                //商品规格总价
                double itemAmount = item.getPrice().doubleValue() * item.getQuantity();
                jdbcTemplate.update(itemSQL, salesOrderId, itemAmount
                        , item.getGoodsId() == null ? 0 : item.getGoodsId()
                        , item.getGoodsTitle()
                        , StringUtils.isEmpty(item.getGoodsNumber()) ? "" : item.getGoodsNumber()
                        , StringUtils.isEmpty(item.getGoodsImage()) ? "" : item.getGoodsImage()
                        , StringUtils.isEmpty(item.getSkuInfo()) ? "" : item.getSkuInfo()
                        , item.getSpecId() == null ? 0 : item.getSpecId()
                        , StringUtils.isEmpty(item.getSpecNumber()) ? "" : item.getSpecNumber()
                        , item.getPrice(), item.getQuantity());
            }

            /**********3、更新天猫订单auditStatus状态为1**********/
            jdbcTemplate.update("UPDATE " + Tables.DcTmallOrder + " SET auditStatus=1,tag=? WHERE id=?","3,6",salesOrder.getOrderNum());
            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "添加订单失败：" + e.getMessage());
        }
    }
    /**
     *
     * @param orderId
     * @param erpGoodsId
     * @param erpGoodSpecId
     * @param quantity
     * @return
     */
    public ResultVo<Long> addOrderGift(Long orderId, Integer erpGoodsId, Integer erpGoodSpecId, Integer quantity){
        if(orderId==null || orderId.longValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少orderId");
        }
        if(erpGoodsId==null || erpGoodsId.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少erpGoodsId");
        }
        if(erpGoodSpecId==null || erpGoodSpecId.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少erpGoodSpecId");
        }
        if(quantity==null || quantity.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少quantity");
        }

        String sql = "SELECT * FROM "+Tables.DcTmallOrder+" WHERE id =? ";
        try {
            var order = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(DcTmallOrderEntity.class),orderId);

            if(order.getAuditStatus().intValue()!=0)
                return new ResultVo<>(EnumResultVo.DataError,"订单已经确认，不允许添加赠品");
            if(order.getStatus() != EnumTmallOrderStatus.WAIT_SEND_GOODS.getStatus() )
                return new ResultVo<>(EnumResultVo.DataError,"订单不是待发货状态，不允许添加赠品");

            /**************************/
            String erpGoodSpecSQL = "SELECT s.*,g.name as goodTitle,g.number as goodsNumber from "+Tables.ErpGoodsSpec+" s LEFT JOIN " + Tables.ErpGoods + " g ON g.id=s.goodsId WHERE s.id=?";
            var erpGoodsSpec = jdbcTemplate.queryForObject(erpGoodSpecSQL,new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),erpGoodSpecId);

            String remark = "添加赠品"+erpGoodsSpec.getId()+"("+erpGoodsSpec.getSpecNumber()+")数量："+quantity;
            String specName = "";
            if(StringUtils.isEmpty(erpGoodsSpec.getColorValue())==false){
                specName += "颜色："+erpGoodsSpec.getColorValue();
            }
            if(StringUtils.isEmpty(erpGoodsSpec.getSizeValue())==false){
                specName += "尺码："+erpGoodsSpec.getSizeValue();
            }
            jdbcTemplate.update("INSERT INTO  "+Tables.DcTmallOrderItem+" SET orderId=?,erpGoodsId=?,erpGoodsSpecId=?,goodsTitle=?,productImgUrl=?,goodsNumber=?,skuInfo=?,specNumber=?,quantity=?,remark=?,price=?,itemAmount=?,isGift=1,subItemId=0"
                    ,orderId
                    ,erpGoodsSpec.getGoodsId(),erpGoodsSpec.getId(),erpGoodsSpec.getGoodTitle(),erpGoodsSpec.getColorImage()
                    ,erpGoodsSpec.getGoodsNumber(),specName,erpGoodsSpec.getSpecNumber(),quantity,remark,new BigDecimal(0),new BigDecimal(0));

            return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，不存在order");
        }
    }

    /**
     * 换货
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> orderItemSwap(OrderItemSwapReq req){

        //查询erp商品规格信息
        ErpGoodsSpecEntity erpGoodsSpec = null;
        try {
            erpGoodsSpec = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), req.getSpecNumber());
        } catch (Exception E) {
            return new ResultVo<>(EnumResultVo.DataError, "SKU【" + req.getSpecNumber() + "】不存在");
        }

        var orderItem = jdbcTemplate.queryForObject("select * from  dc_tmall_order_items where subItemId=?",new BeanPropertyRowMapper<>(DcTmallOrderItemEntity.class),req.getOrderItemId());

        if(orderItem.getIsSwap().intValue()==1)return new ResultVo<>(EnumResultVo.DataError, "商品已经换货");

        var sku = jdbcTemplate.queryForObject("select IFNULL(SUM(currentQty),0) AS currentQty,IFNULL(SUM(lockedQty),0) AS lockedQty from " + Tables.ErpGoodsStockInfo + "  WHERE specId=? AND isDelete = 0", new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), erpGoodsSpec.getId());
        //真实库存=当前库存-(拣货中)锁定库存-待拣货库存
        Integer djhCount = jdbcTemplate.queryForObject(" SELECT IFNULL((select SUM(quantity) from erp_order_item WHERE skuId=? AND (STATUS=0 OR STATUS=1) ),0) djhCount", Integer.class, erpGoodsSpec.getId());
        int currentQty = sku.getCurrentQty().intValue() - (sku.getLockedQty().intValue() + djhCount.intValue());
        if (orderItem.getQuantity().intValue() > currentQty) return new ResultVo<>(EnumResultVo.ParamsError, "仓库商品【" + req.getSpecNumber() + "】库存不足");

        try {
            /***********  插入仓库系统退款订单 erp_order_return    erp_order_return_item  *************/
            //插入仓库退货订单 erp_order_return
            String returnOrder = "INSERT INTO " + Tables.ErpOrderReturn +
                    " (order_num,orderTime,createTime,contactPerson,mobile,address,source,status,logisticsCompany,logisticsCompanyCode,logisticsCode,source_order_num,shopId)" +
                    " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(returnOrder, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, String.valueOf(orderItem.getOrderId()+"_01"));
                    ps.setObject(2, System.currentTimeMillis() / 1000);
                    ps.setLong(3, System.currentTimeMillis() / 1000);
                    ps.setString(4, req.getConsignee());
                    ps.setString(5, req.getMobile());
                    ps.setString(6,req.getAddress());
                    ps.setString(7, ErpOrderSourceEnum.TMALL.getIndex());
                    ps.setInt(8, EnumOrderReturnStatus.WaitReceive.getIndex());
                    ps.setString(9, req.getCompany());
                    ps.setString(10, "");
                    ps.setString(11, req.getCompanyCode());
                    ps.setString(12, orderItem.getOrderId());
                    ps.setInt(13, req.getShopId());
                    return ps;
                }
            }, keyHolder);
            Long returnOrderId = keyHolder.getKey().longValue();

            //插入仓库退货订单明细 erp_order_return_item
            String returnOrderItemSQL = "INSERT INTO " + Tables.ErpOrderReturnItem + " (orderId,goodsId,skuId,skuNumber,quantity,receiveType,createTime,status,inQuantity,badQuantity,refundAmount) VALUE (?,?,?,?,?,?,?,?,?,?,?)";

            //查询仓库系统sku
            var oldSku = jdbcTemplate.queryForObject("select * from " + Tables.ErpGoodsSpec + " WHERE specNumber=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), orderItem.getSpecNumber());
            //更新退货商品信息
            jdbcTemplate.update(returnOrderItemSQL, returnOrderId, oldSku.getGoodsId(), oldSku.getId(), oldSku.getSpecNumber(), orderItem.getQuantity(), 0, System.currentTimeMillis() / 1000, 0, 0, 0,0);

            /*****修改换货状态*****/
            jdbcTemplate.update("update dc_tmall_order_items  set isSwap=1 where subItemId=?",orderItem.getSubItemId());

           /**********1.1、开始插入仓库系统订单表erp_order**********/
            StringBuilder orderInsertSQL = new StringBuilder();
            orderInsertSQL.append("INSERT INTO ").append(Tables.ErpOrder);
            orderInsertSQL.append(" SET ");
            orderInsertSQL.append(" order_num=?,");
            orderInsertSQL.append(" totalAmount=?,");
            orderInsertSQL.append(" shippingFee=?,");
            orderInsertSQL.append(" orderTime=?,");
            orderInsertSQL.append(" createTime=?,");
            orderInsertSQL.append(" modifyTime=?,");
            orderInsertSQL.append(" confirmedTime=?,");
            orderInsertSQL.append(" remark=?,");
            orderInsertSQL.append(" buyerFeedback=?,");
            orderInsertSQL.append(" sellerMemo=?,");
            orderInsertSQL.append(" contactPerson=?,");
            orderInsertSQL.append(" mobile=?,");
            orderInsertSQL.append(" province=?,");
            orderInsertSQL.append(" city=?,");
            orderInsertSQL.append(" area=?,");
            orderInsertSQL.append(" areaCode=?,");
            orderInsertSQL.append(" town=?,");
            orderInsertSQL.append(" townCode=?,");
            orderInsertSQL.append(" address=?,");
            orderInsertSQL.append(" source=?,");
            orderInsertSQL.append(" status=?,");
            orderInsertSQL.append(" shopId=?");

            KeyHolder erpkeyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, orderItem.getOrderId()+"_01");
                    ps.setBigDecimal(2, orderItem.getPrice().multiply(new BigDecimal(orderItem.getQuantity())));
                    ps.setBigDecimal(3, new BigDecimal(0));
                    ps.setLong(4,System.currentTimeMillis() / 1000);
                    ps.setLong(5, System.currentTimeMillis() / 1000);
                    ps.setLong(6, System.currentTimeMillis() / 1000);
                    ps.setLong(7, System.currentTimeMillis() / 1000);
                    ps.setString(8, "");
                    ps.setString(9, "");
                    ps.setString(10,orderItem.getSpecNumber()+"换货");
                    ps.setString(11, req.getConsignee());
                    ps.setString(12, req.getMobile());
                    ps.setString(13, "");
                    ps.setString(14, "");
                    ps.setString(15, "");
                    ps.setString(16, "");
                    ps.setString(17, "");
                    ps.setString(18, "");
                    ps.setString(19, req.getAddress());//addr.get(0).getProvince() + " " + addr.get(0).getCity() + " " + addr.get(0).getArea() + " " +
                    ps.setString(20, ErpOrderSourceEnum.TMALL.getIndex());
                    ps.setInt(21, EnumErpOrderSendStatus.WaitOut.getIndex());
                    ps.setInt(22,req.getShopId());
                    return ps;
                }
            }, erpkeyHolder);

            Long erpOrderId = erpkeyHolder.getKey().longValue();

            /**********1.2、开始插入仓库系统订单明细表erp_order_item**********/
            StringBuilder orderItemInsertSQL = new StringBuilder();
            orderItemInsertSQL.append("INSERT INTO ").append(Tables.ErpOrderItem);
            orderItemInsertSQL.append(" SET ");
            orderItemInsertSQL.append(" orderId=?,");
            orderItemInsertSQL.append(" aliSubItemID=?,");
            orderItemInsertSQL.append(" productMallName=?,");
            orderItemInsertSQL.append(" productId=?,");
            orderItemInsertSQL.append(" productNumber=?,");
            orderItemInsertSQL.append(" skuNumber=?,");
            orderItemInsertSQL.append(" skuId=?,");
            orderItemInsertSQL.append(" productImgUrl=?,");
            orderItemInsertSQL.append(" quantity=?,");
            orderItemInsertSQL.append(" status=?,");
            orderItemInsertSQL.append(" itemAmount=?");

            jdbcTemplate.update(orderItemInsertSQL.toString(),
                    erpOrderId,
                    orderItem.getSubItemId(),
                    orderItem.getGoodsTitle(),
                    erpGoodsSpec.getGoodsId(),//这个字段的值已经变成了仓库系统的goodsId
                    erpGoodsSpec.getGoodsNumber(),
                    req.getSpecNumber(),
                    erpGoodsSpec.getId(),
                    erpGoodsSpec.getColorImage(),
                    orderItem.getQuantity().longValue(),
                    EnumErpOrderSendStatus.WaitOut.getIndex(),
                    orderItem.getPrice().doubleValue()*orderItem.getQuantity());
            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        }catch (Exception ex){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "失败" + ex.getMessage());
        }
    }

    /**
     * 分页查询订单统计明细
     * @param pageIndex
     * @param pageSize
     * @param startTime
     * @param endTime
     * @return
     */
    public PagingResponse<TaoOrderSalesDataEntity> salesOrderDataList(Integer shopId,Integer pageIndex,Integer pageSize,String startTime,String endTime){
        if(shopId == 2|| shopId == 6|| shopId == 7)shopId=2;
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS  * from tao_order_sales_data where shop_id=? ");
        params.add(shopId);
        if (!StringUtils.isEmpty(startTime)) {
            sb.append(" AND date > ?");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime)) {
            sb.append(" AND date <= ?");
            params.add(endTime);
        }
        sb.append(" ORDER BY date desc LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(TaoOrderSalesDataEntity.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();
        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    /**
     * 
     * @param orderId
     * @param company
     * @param code
     */
    public void orderSend(Long orderId,String company,String code){
        
        String sql = "UPDATE dc_tmall_order SET status=?,statusStr=?,logisticsCompany=?,logisticsCode=?,send_time=? WHERE id=? ";
        jdbcTemplate.update(sql,
        EnumTmallOrderStatus.WAIT_BUYER_CONFIRM_GOODS.getStatus(),
        EnumTmallOrderStatus.WAIT_BUYER_CONFIRM_GOODS.getName(),
        company,code,System.currentTimeMillis() / 1000,orderId );
    }

    /**
     * 
     * @param orderId
     * @param company
     * @param code
     */
    public void editRefundLogisticsCode(Long orderId,String company,String code,String address){
        String sql = "UPDATE dc_tmall_order_refund SET logisticsCompany=?,logisticsCode=?,send_time=?,address=? WHERE id=? ";
        jdbcTemplate.update(sql,
        company,code,DateUtil.getCurrentDateTime(),address,orderId );
    }

    public ResultVo<String> signRefund(Long refundId, Integer auditStatus,String remark) {
         //淘宝
         TmallOrderRefundVo refundVo = jdbcTemplate.queryForObject("SELECT * FROM dc_tmall_order_refund WHERE id=? ",new BeanPropertyRowMapper<>(TmallOrderRefundVo.class),refundId);
         
        String desc = refundVo.getDesc(); //jdbcTemplate.queryForObject("SELECT `desc` FROM dc_tmall_order_refund WHERE id=?",String.class, refundId);
        if(StringUtils.isEmpty(desc)) desc="";
        if(StringUtils.isEmpty(remark)) remark="";
        desc = desc +remark;

        if(auditStatus.intValue() == 2){
           

            if(refundVo.getErpGoodsId() ==null || refundVo.getErpGoodsId()<=0)
                return new ResultVo<>(EnumResultVo.DataError,"数据错误，dc_tmall_order_refund中erpGoodsId值错误");
            if(refundVo.getErpGoodsSpecId() ==null || refundVo.getErpGoodsSpecId()<=0)
                return new ResultVo<>(EnumResultVo.DataError,"数据错误，dc_tmall_order_refund中erpGoodsSpecId值错误");
            if(StringUtils.isEmpty(refundVo.getSpecNumber())) return new ResultVo<>(EnumResultVo.DataError,"数据错误，dc_tmall_order_refund中SpecNumber值错误");


            jdbcTemplate.update(
                            "UPDATE dc_tmall_order_refund SET status=?,auditStatus=2,auditTime=?,receivedTime=?,remark=? WHERE refund_id=?",
                            EnumDcTmallOrderReturnStatus.received.getIndex(),
                            DateUtil.getCurrentDateTime(),
                            DateUtil.getCurrentDateTime(),  "确认签收" + DateUtil.getCurrentDateTime(),
                            refundId);
            Integer returnType = 1;
            if(refundVo.getAfterSalesType().intValue() ==3){
                    //换货
                    returnType =2;
            }
            // 2是已签收，下发到仓库系统
            String s = "INSERT INTO erp_order_send_return (orderSn,shopId,goodsId,goodsSpecId,goodsSpecNumber,logisticsCode,quantity,refundAmount,receiveTime,returnType,sourceNo) VALUE (?,?,?,?,?,?,?,?,?,?,?)";
            jdbcTemplate.update(s, refundVo.getTid(), refundVo.getShopId(),
                            refundVo.getErpGoodsId(), refundVo.getErpGoodsSpecId(), refundVo.getSpecNumber(),
                            refundVo.getLogisticsCode(), refundVo.getNum(), refundVo.getRefundFee(),
                            DateUtil.getCurrentDateTime(),returnType,refundVo.getRefundId());
        }else{
            jdbcTemplate.update("UPDATE dc_tmall_order_refund SET auditStatus=?,auditTime=?,`desc`= ? WHERE id=?",
                auditStatus,
                DateUtil.getCurrentDateTime(),remark,
                refundId);
        }
    
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

    /**
     * 导入excel订单
     *
     * @param orderList   订单列表
     * @return
     */
    @Transactional
    public ResultVo<String> importExcelOrder(List<OrderImportPiPiEntity> orderList,Integer shopId) {
        if (orderList == null || orderList.size() == 0)
            return new ResultVo<>(EnumResultVo.DataError, "参数错误：缺少orderList","");

        /*******插入数据********/
        int totalInsert = 0;//新增数量
        int totalExist = 0;//已存在数量
        int totalError = 0;//错误数量
        for (var order : orderList) {
            //查询订单是否存在
            var oList = jdbcTemplate.query("SELECT * FROM dc_tmall_order WHERE id=? ", new BeanPropertyRowMapper<>(OrderImportPiPiEntity.class), order.getOrderNum());

            if (oList != null && oList.size() > 0) {
                //已经存在
                totalExist++;
            } else {

                try {
                    /**************1、新增order**********************/
                    StringBuilder insertSQL = new StringBuilder();
                    insertSQL.append("INSERT INTO dc_tmall_order ");
                    insertSQL.append(" (id,buyerName,totalAmount,shippingFee,createTime,payTime,payAmount,sellerMemo,buyerFeedback,");
                    insertSQL.append("statusStr,status,logisticsCompany,logisticsCode,auditStatus,");
                    insertSQL.append("createOn,shopId)");
                    insertSQL.append(" VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");

                    KeyHolder keyHolder = new GeneratedKeyHolder();
                    jdbcTemplate.update(new PreparedStatementCreator() {
                        @Override
                        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                            PreparedStatement ps = connection.prepareStatement(insertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                            ps.setLong(1,Long.parseLong(order.getOrderNum()));
                            ps.setString(2,  order.getBuyerName());
                            ps.setBigDecimal(3, new BigDecimal(order.getOrderAmount()));
                            ps.setBigDecimal(4, new BigDecimal(order.getExpressFee()));
                            ps.setString(5, order.getOrderTimeStr());
                            ps.setString(6, order.getPayTime());
                            ps.setBigDecimal(7, new BigDecimal(order.getPayAmount()));
                            ps.setString(8, order.getSellerMemo());
                            ps.setString(9, order.getBuyerFeedback());
                            ps.setString(10, order.getStatusStr());
                            ps.setInt(11, order.getStatus());
                            ps.setString(12, order.getLogisticsCompany());
                            ps.setString(13, order.getLogisticsCode());
                            ps.setInt(14, 0);
                            ps.setLong(15, System.currentTimeMillis() / 1000);
                            ps.setInt(16, shopId);
                            return ps;
                        }
                    }, keyHolder);

                 

                    /*******************2、添加order_item**************************/
                    //添加订单明细
                    String itemSQL = "INSERT INTO dc_tmall_order_items (orderId,subItemId,itemAmount,erpGoodsId,goodsTitle,goodsNumber,"+
                    "productImgUrl,skuInfo,price,quantity,skuId,specNumber,refundStatusStr) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    for (var item : order.getItems()) {
                        jdbcTemplate.update(itemSQL, order.getOrderNum(),item.getSubOrderNum(),item.getPayAmount()
                                ,item.getGoodsId(), item.getGoodsTitle(), item.getGoodsNumber(), item.getGoodsImg()
                                ,item.getSkuInfo(),item.getPrice(), item.getQuantity()
                                ,item.getSpecId(),item.getSpecNumber(),"NO_REFUND");
                    }

                    /*******************3、添加order_address**************************/
                    String addressSQL = "INSERT INTO dc_tmall_order_address (orderId,contactPerson,mobile,province,city,area,address) VALUE (?,?,?,?,?,?,?)";
                    jdbcTemplate.update(addressSQL,order.getOrderNum(),order.getContactPerson(),order.getContactMobile(),order.getProvince(),order.getCity(),order.getArea(),order.getAddress());

                    totalInsert++;//新增成功

                } catch (Exception e) {
                    totalError++;
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return new ResultVo<>(EnumResultVo.SystemException, "系统异常：" + e.getMessage(),"");
                }
            }
        }
        String msg = "新增成功：" + totalInsert + "，新增失败：" + totalError + "，已存在：" + totalExist;
////        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS", msg);

    }
}
